﻿using Coldairarrow.Business.AOP;
using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.FP;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_DeliveryNoticeBusiness : BaseBusiness<PB_DeliveryNotice>, IPB_DeliveryNoticeBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        public PB_DeliveryNoticeBusiness(IDbAccessor db, IOperator @operator)
            : base(db)
        {
            _operator = @operator;

        }

        #region 外部接口

        public async Task<PageResult<RealNameDeliveryNoticeDTO>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            //var q = GetIQueryable();
            //var where = LinqHelper.True<PB_DeliveryNotice>();
            //var search = input.Search;
            //var newWhereType = DynamicExpressionParser.ParseLambda<PB_DeliveryNotice, bool>(
            //     ParsingConfig.Default, false, $@"type.Equals(@0)", 1);
            //where = where.And(newWhereType);
            ////筛选
            //if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            //{
            //    var newWhere = DynamicExpressionParser.ParseLambda<PB_DeliveryNotice, bool>(
            //        ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
            //    where = where.And(newWhere);
            //}



            var where = LinqHelper.True<RealNameDeliveryNoticeDTO>();
            var search = input.Search;


            Expression<Func<PB_DeliveryNotice, Base_User,Base_OverFlow, RealNameDeliveryNoticeDTO>> select = (e, f,o) => new RealNameDeliveryNoticeDTO
            {

                Realname = f.RealName , //
                WorkFlowName=o.Title,
            };
            select = select.BuildExtendSelectExpre();
            var q = from e in GetIQueryable().AsExpandable()
                    join f in Db.GetIQueryable<Base_User>() on e.CreatorId equals f.Id into ef
                    from f in ef.DefaultIfEmpty()
                    join o in Db.GetIQueryable<Base_OverFlow>() on e.WorkFlow equals o.Id into eo
                    from o in eo.DefaultIfEmpty()
                    select @select.Invoke(e, f, o);
        
            var newWhereType = DynamicExpressionParser.ParseLambda<RealNameDeliveryNoticeDTO, bool>(
                  ParsingConfig.Default, false, $@"type.Equals(@0)", 1);
            where = where.And(newWhereType);
            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<RealNameDeliveryNoticeDTO, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword.Trim());
                where = where.And(newWhere);
            }
            //string time = "ApprovalTime";
            //var OrderByApprovalTile= DY.ParseLambda<RealNameDeliveryNoticeDTO, bool>(
            //      ParsingConfig.Default, false, $@"order   @0", time);
            //where = where.And(OrderByApprovalTile);
            //where = where.And(d => _operator.Property.DeptIds.Contains(d.DeptId));
            return await q.Where(where).GetPageResultAsync(input);

          

            //return await q.Where(where).GetPageResultAsync(input);
        }
        public async Task<PageResult<PB_DeliveryNotice>> GetDataListReturnAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_DeliveryNotice>();
            var search = input.Search;
            var newWhereType = DynamicExpressionParser.ParseLambda<PB_DeliveryNotice, bool>(
                   ParsingConfig.Default, false, $@"type.Equals(@0)", 2);
            where = where.And(newWhereType);
            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_DeliveryNotice, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_DeliveryNotice> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }
        public async Task<PageResult<PB_DesignDetailItemDTO>> GetTheDataitemAsync(PageInput<PB_DesignDetailselectDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_ShippingList, PB_Material,PB_DesignDetail, PB_DeliveryNoticeItem, PB_DesignDetailItemDTO>> select = (a,b,c,d) => new PB_DesignDetailItemDTO
            {

                //MaterialID = b.Id,//物料编号
                MaterialName = b.Name,//物料名称
                Code = b.Code,//物料编号
                Spec = b.Spec, //物料规格
                ProductCode = b.ProductCode, //成品编码
                RealNums =c.RealNums, //应发
                ShipmentNums = c.ShipmentNums, //已发
                NoticeItemId = d.ParentId, //通知单ID
                BuildingNo = d.BuildingNo ,//楼号
                Slot=c.Slot,//设计清单详情排序字段
                num2=c.RealNums-c.ShipmentNums,//未发数量
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_ShippingList>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_DesignDetail>() on a.SubId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_DeliveryNoticeItem>() on a.ParentId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a,b,c,d);
            if (!search.Id.IsNullOrEmpty())
            {
                q = q.Where(x => x.ParentId == search.Id);
            }
            if (!search.NoticeItemId.IsNullOrEmpty())
            {
                q = q.Where(x => x.NoticeItemId == search.NoticeItemId);
            }
            return await q.GetPageResultAsync(input);

        }
        
        public async Task AddDataAsync(PB_DeliveryNoticeMasterSubDTO data)
        {

            var userdata =await Db.GetIQueryable<Base_User>().Where(m=>m.Id==_operator.UserId).FirstOrDefaultAsync() ;
            List<PB_DeliveryNoticeItem> ListDeliveryNoticeItem = new List<PB_DeliveryNoticeItem>();
            #region 发货通知单
            PB_DeliveryNotice pb_DeliveryNotice = new PB_DeliveryNotice();
            string dataId = IdHelper.GetId();
            pb_DeliveryNotice.Id = dataId;
            pb_DeliveryNotice.CreatorId = _operator?.UserId;
            pb_DeliveryNotice.CreatorName = _operator?.Property.UserName;
            pb_DeliveryNotice.ProjectId = data.main.ProjectId;
            pb_DeliveryNotice.ProjectName = data.main.ProjectName;
            pb_DeliveryNotice.Address = data.main.Address;
            pb_DeliveryNotice.Remark = data.main.Remark;
            pb_DeliveryNotice.IssueDate = data.main.IssueDate;
            pb_DeliveryNotice.ContractNo = data.main.ContractNo;
            pb_DeliveryNotice.TypeId = data.main.TypeId;
            pb_DeliveryNotice.SupplierName = data.main.SupplierName;
            pb_DeliveryNotice.SignUnitName = data.main.SignUnitName;
            pb_DeliveryNotice.CooperativeUnitName = data.main.CooperativeUnitName;
            pb_DeliveryNotice.SaleUnitName = data.main.SaleUnitName;
            pb_DeliveryNotice.SupplierId = data.main.SupplierId;
            pb_DeliveryNotice.SignUnitId = data.main.SignUnitId;
            pb_DeliveryNotice.CooperativeUnitId = data.main.CooperativeUnitId;
            pb_DeliveryNotice.SaleUnitId = data.main.SaleUnitId;
            pb_DeliveryNotice.ShipmentStatus = 0;
            pb_DeliveryNotice.AuditStatus = 0;
            pb_DeliveryNotice.Deleted = false;
            pb_DeliveryNotice.ConsigneeName = data.main.ConsigneeName;
            pb_DeliveryNotice.ConsigneeTelephone = data.main.ConsigneeTelephone;
            pb_DeliveryNotice.ConsigneeId = data.main.ConsigneeId;
            pb_DeliveryNotice.ReturnTonnage= data.main.ReturnTonnage;
            pb_DeliveryNotice.Type = data.main.Type;
            pb_DeliveryNotice.CreateTime = DateTime.Now;
            pb_DeliveryNotice.UpdateTime = DateTime.Now;
            pb_DeliveryNotice.FreightDetails = data.main.FreightDetails;
            pb_DeliveryNotice.WorkFlow = data.main.WorkFlow;
            pb_DeliveryNotice.QrCode = PB_DeliveryNoticeCode + ',' + dataId;
            pb_DeliveryNotice.Remarks = data.main.Remarks;
            pb_DeliveryNotice.BusinessLeader = data.main.BusinessLeader;
            pb_DeliveryNotice.DocumentaryContact = data.main.DocumentaryContact;
            pb_DeliveryNotice.ConsigneeOfParty = data.main.ConsigneeOfParty;
            pb_DeliveryNotice.Remarks = data.main.Remarks;
            pb_DeliveryNotice.ApprovalTime = data.main.ApprovalTime;
            pb_DeliveryNotice.DeptId = userdata.FactoryId;//绑定所属工厂
            #endregion
            #region 组装数据
            string Abstract = "";

            //2022-03-04 朱柏清 项目执行过程明细
            List<PB_ProjectProcess> pB_ProjectProcesseslist = new List<PB_ProjectProcess>();
            for (int i = 0; i < data.son?.Count; i++)
            {
                PB_DeliveryNoticeItem PB_DeliveryNoticeItem = new PB_DeliveryNoticeItem();
                string inID= IdHelper.GetId();//详情id
                PB_DeliveryNoticeItem.Id = inID;
                PB_DeliveryNoticeItem.CreateTime = DateTime.Now;//设计清单主表id
                PB_DeliveryNoticeItem.CreatorId = _operator?.UserId; //创建人
                PB_DeliveryNoticeItem.CreatorName = _operator?.Property.UserName;
                PB_DeliveryNoticeItem.Deleted = false;//物料id
                PB_DeliveryNoticeItem.ParentId = dataId;
                if (data.son?.Count == 1)
                {
                    Abstract += data.son?[i].BuildingNo;
                }
                else
                {
                    Abstract += data.son?[i].BuildingNo + ",";
                }
                if (!string.IsNullOrEmpty(data.son?[i].DesignId))
                {
                    await GenerationMethod(data.son?[i].DesignId, inID);

                    var datasubMData = Db.GetListBySqlAsync<PB_DesignListProject>(@"SELECT  ProjectId,BuildingNumberID FROM `PB_DesignList` where id='"+data.son?[i].DesignId+"'").Result;
                    for (int k = 0; k < datasubMData?.Count; k++)
                    {
                        var ProjectId = datasubMData[k].ProjectId;
                        var BuilId = datasubMData[k].BuildingNumberID;
                        string sqlMain = @" update PB_DeliveryPlanDetails set `States`=2    WHERE ProjectId='" + ProjectId + "' and BuildingId='" + BuilId + "' ";
                        await ExecuteSqlAsync(sqlMain);
                    }
                }
                PB_DeliveryNoticeItem.DesignId = data.son?[i].DesignId;
                PB_DeliveryNoticeItem.BuildingNo = data.son?[i].BuildingNo;
                PB_DeliveryNoticeItem.InDate = data.son?[i].InDate;
                PB_DeliveryNoticeItem.Requirement = data.son?[i].Requirement;
                PB_DeliveryNoticeItem.Remark = data.son?[i].Remark;
                PB_DeliveryNoticeItem.ShipmentStatus = 0;
                PB_DeliveryNoticeItem.AuditStatus = 0;
                PB_DeliveryNoticeItem.NumberIn = data.son?[i].NumberIn;
                PB_DeliveryNoticeItem.ListSubName = data.son?[i].ListSubName;
                PB_DeliveryNoticeItem.DesignListType = data.son?[i].DesignListType;
                PB_DeliveryNoticeItem.BuiName = data.son?[i].BuiName;
                PB_DeliveryNoticeItem.DeptId = userdata.FactoryId;//绑定所属工厂列
                ListDeliveryNoticeItem.Add(PB_DeliveryNoticeItem);

                //2022-03-04 朱柏清 项目执行过程明细               
                PB_DesignList pB_DesignList = Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == data.son[i].DesignId).FirstOrDefault();
                PB_ProjectProcess pB_ProjectProcess = new PB_ProjectProcess
                {
                    Id = IdHelper.GetId(),
                    ProjectId = pB_DesignList.ProjectId,
                    ProjectName = pB_DesignList.ProjectName,
                    ProcessType = Db.GetIQueryable<Base_EnumItem>().Where(u => u.EnumCode.Contains("ProjectProcessType") && u.Name.Contains("发货通知")).FirstOrDefault().Value,
                    BuildingnumberId = pB_DesignList.BuildingNumberID,
                    BuildingnumberName = Db.GetIQueryable<PB_Buildingnumber>().Where(u => u.Id == pB_DesignList.BuildingNumberID).FirstOrDefault().Buildingnumber,
                    Remarks = "新增",
                    CreatorId = _operator.UserId,
                    CreatorRealName = _operator.Property.RealName,
                    CreateTime = DateTime.Now,
                    OrderId= inID,
                    Deleted = false,
                };
                pB_ProjectProcesseslist.Add(pB_ProjectProcess);
               
            }

            await Db.InsertAsync<PB_ProjectProcess>(pB_ProjectProcesseslist);
            pb_DeliveryNotice.Abstract = data.main.Abstract == null ? data.main.ProjectName + ",楼号:[" + Abstract + "]" : data.main.Abstract;
            //await Db.InsertAsync<PB_DeliveryNotice>(pb_DeliveryNotice);
            await InsertAsync(pb_DeliveryNotice);
            await Db.InsertAsync<PB_DeliveryNoticeItem>(ListDeliveryNoticeItem);



            


            #endregion
        }
        public async Task GenerationMethod(string ManifestID,string ParentId)
        {
            if (string.IsNullOrEmpty(ManifestID) || string.IsNullOrEmpty(ParentId))
            {
                return;
            }
            var datasubMDatacount = Db.GetListBySqlAsync<PB_ShippingList>(@" SELECT  *  FROM PB_ShippingList WHERE ParentId ='"+ ParentId + "'").Result;
            if (datasubMDatacount.Count > 0)
            {
                return;
            }

            var datasubMData = Db.GetListBySqlAsync<PB_DesignMaterialSave>(@"select a.Id,a.Id SubId,a.MaterialId,b.`Name` MaterialName,b.`Code` MaterialCode,ifnull(b.Weight,0) Weight,b.StorageId,a.Nums,a.RealNums,ifnull(c.EstimatedQuantity,0) EstimatedQuantity,ifnull(a.ShipmentNums,0) ShipmentNums 
               from PB_DesignDetail a 
            left join PB_Material b on a.MaterialId=b.Id 
               left join PB_ShippingList c on a.Id=c.SubId 
             where a.ParentId='"+ ManifestID + "'").Result;

            List<PB_ShippingList> ListDesignDetail = new List<PB_ShippingList>();

            PB_WarehousingNotice pB_WarehousingNotice = new PB_WarehousingNotice();
            string dataId = IdHelper.GetId();
            pB_WarehousingNotice.Id = dataId;
            pB_WarehousingNotice.QrCode = "20," + dataId;
            pB_WarehousingNotice.DeptId = _operator.Property.DepartmentId;
            pB_WarehousingNotice.CreatorName = _operator.Property.UserName;
            pB_WarehousingNotice.CreateTime = DateTime.Now;
            pB_WarehousingNotice.CreatorId = _operator.UserId;
            pB_WarehousingNotice.Deleted = false;
            pB_WarehousingNotice.Type = 3;

            await Db.InsertAsync<PB_WarehousingNotice>(pB_WarehousingNotice);

            for (int i = 0; i < datasubMData?.Count; i++)
            {
                PB_ShippingList pB_DesignDetail = new PB_ShippingList();
                pB_DesignDetail.Id = IdHelper.GetId();//详情id
                pB_DesignDetail.ParentId = ParentId;//
                pB_DesignDetail.SubId = datasubMData[i].SubId;
                pB_DesignDetail.MaterialId = datasubMData[i].MaterialId;//物料id
                pB_DesignDetail.Nums = datasubMData[i].Nums;//设计数量
                pB_DesignDetail.EstimatedQuantity = datasubMData[i].RealNums;//实发数量
                pB_DesignDetail.Remarks = datasubMData[i].Remarks;//备注
                pB_DesignDetail.CreateTime = DateTime.Now;//创建时间
                pB_DesignDetail.CreatorId = _operator?.UserId; //创建人
                pB_DesignDetail.Deleted = false;//伪删除
                pB_DesignDetail.Weight = datasubMData[i].Weight;//理论重量
                pB_DesignDetail.OddNumber = dataId;//自动生成不做动作关联了。 整体设计不受控制
                ListDesignDetail.Add(pB_DesignDetail);
            }
           
            await Db.InsertAsync<PB_ShippingList>(ListDesignDetail);
        }

        public async Task UpdateDataAsync(PB_DeliveryNoticeMasterSubDTO data)
        {
            var userdata =await Db.GetIQueryable<Base_User>().Where(m=>m.Id==_operator.UserId).FirstOrDefaultAsync();
            List<string> ItemIdinfo = new List<string>();
            List<PB_DeliveryNoticeItem> ListDeliveryNoticeItemI = new List<PB_DeliveryNoticeItem>();
            List<PB_DeliveryNoticeItem> ListDeliveryNoticeItemU = new List<PB_DeliveryNoticeItem>();
            #region 发货通知单
            PB_DeliveryNotice pb_DeliveryNotice = new PB_DeliveryNotice();
            //string dataId = IdHelper.GetId();
            pb_DeliveryNotice.Id = data.main.Id;
            pb_DeliveryNotice.CreatorName = _operator.Property.UserName;
            pb_DeliveryNotice.CreatorId = data.main.CreatorId;
            pb_DeliveryNotice.CreatorName = data.main.CreatorName;
            pb_DeliveryNotice.ProjectId = data.main.ProjectId;
            pb_DeliveryNotice.ProjectName = data.main.ProjectName;
            pb_DeliveryNotice.Address = data.main.Address;
            pb_DeliveryNotice.Remark = data.main.Remark;
            pb_DeliveryNotice.IssueDate = data.main.IssueDate;
            pb_DeliveryNotice.ContractNo = data.main.ContractNo;
            pb_DeliveryNotice.TypeId = data.main.TypeId;
            pb_DeliveryNotice.SupplierName = data.main.SupplierName;
            pb_DeliveryNotice.SignUnitName = data.main.SignUnitName;
            pb_DeliveryNotice.CooperativeUnitName = data.main.CooperativeUnitName;
            pb_DeliveryNotice.SaleUnitName = data.main.SaleUnitName;
            pb_DeliveryNotice.SupplierId = data.main.SupplierId;
            pb_DeliveryNotice.SignUnitId = data.main.SignUnitId;
            pb_DeliveryNotice.CooperativeUnitId = data.main.CooperativeUnitId;
            pb_DeliveryNotice.SaleUnitId = data.main.SaleUnitId;
            pb_DeliveryNotice.ReturnTonnage = data.main.ReturnTonnage;
            pb_DeliveryNotice.ShipmentStatus = 0;
            pb_DeliveryNotice.AuditStatus = 0;
            pb_DeliveryNotice.ConsigneeName = data.main.ConsigneeName;
            pb_DeliveryNotice.ConsigneeId = data.main.ConsigneeId;
            pb_DeliveryNotice.ConsigneeTelephone = data.main.ConsigneeTelephone;
            pb_DeliveryNotice.Type = data.main.Type;
            pb_DeliveryNotice.Deleted = false;
            pb_DeliveryNotice.UpdateTime = DateTime.Now;
            pb_DeliveryNotice.FreightDetails = data.main.FreightDetails;
            pb_DeliveryNotice.BusinessLeader = data.main.BusinessLeader;
            pb_DeliveryNotice.DocumentaryContact = data.main.DocumentaryContact;
            pb_DeliveryNotice.ConsigneeOfParty = data.main.ConsigneeOfParty;
            pb_DeliveryNotice.WorkFlow = data.main.WorkFlow;
            pb_DeliveryNotice.Remarks = data.main.Remarks;
            pb_DeliveryNotice.ApprovalTime = data.main.ApprovalTime;
            if (!data.main.DelayDescription.IsNullOrEmpty())
            {
                pb_DeliveryNotice.DelayDescription = data.main.DelayDescription;
                pb_DeliveryNotice.AuditStatus = 1;
            }
            else {
                pb_DeliveryNotice.DelayDescription = null;
            }
            if (!data.main.DelayTime.IsNullOrEmpty())
            {
                pb_DeliveryNotice.DelayTime = data.main.DelayTime;
            }
            else
            {
                pb_DeliveryNotice.DelayDescription = null;
            }
            pb_DeliveryNotice.DeptId = userdata.FactoryId;//绑定当前登陆人所属工厂
            string Abstract = "";
            #endregion
            #region 组装数据
            //2022-03-04 朱柏清 项目执行过程明细
            List<PB_ProjectProcess> pB_ProjectProcesseslist = new List<PB_ProjectProcess>();
            for (int i = 0; i < data.son?.Count; i++)
            {
                PB_DeliveryNoticeItem PB_DeliveryNoticeItem = new PB_DeliveryNoticeItem();
                if (data.son?[i].Id != null)
                {
                    PB_DeliveryNoticeItem.Id = data.son?[i].Id;//详情id
                    ItemIdinfo.Add(PB_DeliveryNoticeItem.Id.ToString());

                }
                else {
                    PB_DeliveryNoticeItem.Id= IdHelper.GetId();

                    //2022-03-04 朱柏清 项目执行过程明细               
                    PB_DesignList pB_DesignList = Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == data.son[i].DesignId).FirstOrDefault();
                    PB_ProjectProcess pB_ProjectProcess = new PB_ProjectProcess
                    {
                        Id = IdHelper.GetId(),
                        ProjectId = pB_DesignList.ProjectId,
                        ProjectName = pB_DesignList.ProjectName,
                        ProcessType = Db.GetIQueryable<Base_EnumItem>().Where(u => u.EnumCode.Contains("ProjectProcessType") && u.Name.Contains("发货通知")).FirstOrDefault().Value,
                        BuildingnumberId = pB_DesignList.BuildingNumberID,
                        BuildingnumberName = Db.GetIQueryable<PB_Buildingnumber>().Where(u => u.Id == pB_DesignList.BuildingNumberID).FirstOrDefault().Buildingnumber,
                        Remarks = "新增",
                        CreatorId = _operator.UserId,
                        CreatorRealName = _operator.Property.RealName,
                        CreateTime = DateTime.Now,
                        OrderId = PB_DeliveryNoticeItem.Id,
                        Deleted = false,
                    };
                    pB_ProjectProcesseslist.Add(pB_ProjectProcess);
                }
                PB_DeliveryNoticeItem.CreateTime = DateTime.Now;//设计清单主表id
                PB_DeliveryNoticeItem.CreatorId = _operator?.UserId; //创建人
                PB_DeliveryNoticeItem.CreatorName = _operator?.Property.UserName;
                PB_DeliveryNoticeItem.Deleted = false;//物料id
                PB_DeliveryNoticeItem.ParentId = data.main.Id;
                PB_DeliveryNoticeItem.DesignId = data.son?[i].DesignId;
                PB_DeliveryNoticeItem.BuildingNo = data.son?[i].BuildingNo;
                if (data.son?.Count == 1)
                {
                    Abstract += data.son?[i].BuildingNo;
                }
                else {
                    Abstract += data.son?[i].BuildingNo + ",";
                }
                PB_DeliveryNoticeItem.InDate = data.son?[i].InDate;
                PB_DeliveryNoticeItem.Requirement = data.son?[i].Requirement;
                PB_DeliveryNoticeItem.Remark = data.son?[i].Remark;
                PB_DeliveryNoticeItem.ShipmentStatus = 0;
                PB_DeliveryNoticeItem.AuditStatus = 0;
                PB_DeliveryNoticeItem.NumberIn = data.son?[i].NumberIn;
                PB_DeliveryNoticeItem.ListSubName = data.son?[i].ListSubName;
                PB_DeliveryNoticeItem.DesignListType = data.son?[i].DesignListType;
                PB_DeliveryNoticeItem.BuiName = data.son?[i].BuiName;
                if (data.son?[i].Id != null)
                {
                    ListDeliveryNoticeItemU.Add(PB_DeliveryNoticeItem);
                }
                else
                {
                    ListDeliveryNoticeItemI.Add(PB_DeliveryNoticeItem);
                }
                PB_DeliveryNoticeItem.DeptId = userdata.FactoryId;
            }
            await Db.InsertAsync<PB_ProjectProcess>(pB_ProjectProcesseslist);
            pb_DeliveryNotice.Abstract = data.main.Abstract==null? data.main.ProjectName + ",楼号:[" + Abstract+"]": data.main.Abstract;
            await UpdateAsync(pb_DeliveryNotice);
            //没有子数据说明修改的是回货单
            if (data.son== null)
            {
                List<string> props = GetFieldList(pb_DeliveryNotice);

                await UpdateAsync(pb_DeliveryNotice, props);
                return;
            }
            if (ListDeliveryNoticeItemU != null)
            {

                var intruntinfo = await ExecuteSqlAsync(" DELETE FROM PB_DeliveryNoticeItem WHERE ParentId = '" + data.main.Id + "' and id not in (" + String.Join(",", ItemIdinfo.ToArray()) + ")");
                await Db.UpdateAsync<PB_DeliveryNoticeItem>(ListDeliveryNoticeItemU);

            }
            if (ListDeliveryNoticeItemI != null)
            {
              int a =  await Db.InsertAsync<PB_DeliveryNoticeItem>(ListDeliveryNoticeItemI);

            }
            #endregion
        }

        public async Task<AjaxResult> DeleteDataAsync(List<string> ids)
        {
            AjaxResult res = new AjaxResult
            {
                Success = true,
                Msg = "请求成功！"
            };
            AjaxResult resReturn = new AjaxResult
            {
                Success = false,
                Msg = "已经做了备货单！不可删除"
            };
            //根数据
            var atAllData = Db.GetListBySqlAsync<PB_PickUpList>("SELECT  a.* FROM PB_PickUpMain a INNER JOIN PB_DeliveryNoticeItem b on a.ParentId = b.Id WHERE b.ParentId in (" + String.Join(", ", ids).Replace("\"", "'") + ") ").Result;
            if (atAllData.Count > 0)
            {
                return resReturn;
            }
            //2022-03-04 朱柏清 项目执行过程明细
            foreach (var id in ids)
            {
                List<PB_DeliveryNoticeItem> pB_DeliveryNotice = await Db.GetIQueryable<PB_DeliveryNoticeItem>().Where(u => u.ParentId == id).ToListAsync();
                List<PB_ProjectProcess> pB_ProjectProcesslist = new List<PB_ProjectProcess>();
                foreach (var item in pB_DeliveryNotice)
                {
                    PB_DesignList pB_DesignList = Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == item.DesignId).FirstOrDefault();
                    PB_ProjectProcess pB_ProjectProcess = new PB_ProjectProcess
                    {
                        Id = IdHelper.GetId(),
                        ProjectId = pB_DesignList.ProjectId,
                        ProjectName = pB_DesignList.ProjectName,
                        ProcessType = Db.GetIQueryable<Base_EnumItem>().Where(u => u.EnumCode.Contains("ProjectProcessType") && u.Name.Contains("发货通知")).FirstOrDefault().Value,
                        BuildingnumberId = pB_DesignList.BuildingNumberID,
                        BuildingnumberName = Db.GetIQueryable<PB_Buildingnumber>().Where(u => u.Id == pB_DesignList.BuildingNumberID).FirstOrDefault().Buildingnumber,
                        Remarks = "删除",
                        CreatorId = _operator.UserId,
                        CreatorRealName = _operator.Property.RealName,
                        CreateTime = DateTime.Now,
                        OrderId = id,
                        Deleted = false,
                    };
                    pB_ProjectProcesslist.Add(pB_ProjectProcess);
                }
                await Db.InsertAsync<PB_ProjectProcess>(pB_ProjectProcesslist);
            }
            //先删除原有的关联数据
            var where = LinqHelper.True<PB_DeliveryNoticeItem>();
            if (!string.Join(",", ids.Select(q => "'" + q + "'").ToArray()).IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_DeliveryNoticeItem, bool>(
                        ParsingConfig.Default, false, $@"ParentId.Equals(@0)", string.Join(",", ids.Select(q => "'" + q + "'").ToArray()));
                where = where.And(newWhere);
            }
            var DbInfo = Db.GetIQueryable<PB_ShippingList>();
            string strid = "";
            foreach (var item in ids)
            {
                strid += "'" + item + "',";
            }
            strid = strid.Substring(0, strid.Length - 1);
            await ExecuteSqlAsync("delete from PB_DeliveryNoticeItem where ParentId in (" + strid + ")");
            await DeleteAsync(ids);
            return res;
        }
        [OverFlowBegin("DeliveryNotice")]
        public async Task<AjaxResult> SaveToExamine(string Id, bool judge)
        {
            int Return = 0;
            if (judge)
            {
                Return = Db.ExecuteSql("UPDATE  PB_DeliveryNotice set AuditStatus=1,UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'  where Id='" + Id + "' ");


                //推送第一级
                List<string> ulist = new List<string>();
                //获取进场通知单数据
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT ");
                sql.Append("b.Id,");
                sql.Append("b.Content,");
                sql.Append("ifnull(c.LEVEL,0) LEVEL ");
                sql.Append("FROM ");
                sql.Append("PB_DeliveryNotice a ");
                sql.Append("LEFT JOIN Base_OverFlow b ON a.WorkFlow = b.Id ");
                sql.Append("LEFT JOIN Base_OverFlowTask c ON a.Id = c.BillId  ");
                sql.Append("WHERE ");
                sql.Append("a.Id = '" + Id +"'");
                Util.Helper.DingInputDTO input = new Util.Helper.DingInputDTO();
                string uids = "";
                var flowMob = await Db.GetListBySqlAsync<CurrentOverFlowDTO>(sql.ToString());
                JArray farrMob = JArray.Parse(flowMob[0].Content);
                JObject _objMob = JObject.Parse(farrMob[flowMob[0].Level].ToString());
                JArray usrMob = JArray.Parse(farrMob[0]["Assignees"]["UserId"].ToString());
                //推送显示文本
                var Ttile =await Db.GetIQueryable<PB_DeliveryNotice>().Where(x=>x.Id==Id).FirstOrDefaultAsync();
                string title = Ttile.ProjectName;
                var aa = Db.GetIQueryable<PB_DeliveryNoticeItem>().Where(m => m.ParentId == Id).ToList();
                foreach (var tiem in aa) {
                    title += tiem.BuildingNo;
                }
                input.Str = $"发货通知:{title} 待审批"+ DateTime.Now.ToString("");
                string url= "eapp://pages/info/info?billid="+Id+"&title="+ _objMob["StartState"]["Name"]+"&btn="+ _objMob["Action"]["Name"].ToString() +"&id="+ flowMob[0].Id +"";

                //input.MessgUrl="dingtalk://dingtalkclient/page/link?url=" + UrlEncode(url);
                input.MessgUrl = url;
           
                //推送表单
                input.BillId = Id;
                //获取创建进场通知单创建者
                if (usrMob.Count > 0)
                {
                    foreach (var u in usrMob)
                    {
                        uids += "'" + u + "',";
                    }
                    var emps = await Db.GetListBySqlAsync<Base_User>("SELECT * FROM Base_User WHERE Id IN (" + uids.Substring(0, uids.Length - 1) + ")");
                    foreach (var e in emps)
                    {
                        ulist.Add(e.DingUserId);
                    }
                }

                //推送用户集合
                input.ExecutorIds = ulist;
                //钉钉推送消息
                DingHelper.sendMessage(input);
            }
            else
            {
                Return = Db.ExecuteSql("UPDATE PB_DeliveryNotice  set AuditStatus=2 ,UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'  where Id='" + Id + "' ");
            }
            if (Return > 0)
            {
                //2022-03-04 朱柏清 项目执行过程明细
                List<PB_DeliveryNoticeItem> pB_DeliveryNotice = await Db.GetIQueryable<PB_DeliveryNoticeItem>().Where(u => u.ParentId == Id).ToListAsync();
                List<PB_ProjectProcess> pB_ProjectProcesslist = new List<PB_ProjectProcess>();
                foreach (var item in pB_DeliveryNotice)
                {
                    PB_DesignList pB_DesignList = Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == item.DesignId).FirstOrDefault();
                    PB_ProjectProcess pB_ProjectProcess = new PB_ProjectProcess
                    {
                        Id = IdHelper.GetId(),
                        ProjectId = pB_DesignList.ProjectId,
                        ProjectName = pB_DesignList.ProjectName,
                        ProcessType = Db.GetIQueryable<Base_EnumItem>().Where(u => u.EnumCode.Contains("ProjectProcessType") && u.Name.Contains("发货通知")).FirstOrDefault().Value,
                        BuildingnumberId = pB_DesignList.BuildingNumberID,
                        BuildingnumberName = Db.GetIQueryable<PB_Buildingnumber>().Where(u => u.Id == pB_DesignList.BuildingNumberID).FirstOrDefault().Buildingnumber,
                        Remarks = "审核",
                        CreatorId = _operator.UserId,
                        CreatorRealName = _operator.Property.RealName,
                        CreateTime = DateTime.Now,
                        OrderId=item.Id,
                        Deleted = false,
                    };
                    pB_ProjectProcesslist.Add(pB_ProjectProcess);
                }
                await Db.InsertAsync<PB_ProjectProcess>(pB_ProjectProcesslist);
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string UrlEncode(string str)
        {
            StringBuilder sb = new StringBuilder();
            byte[] byStr = System.Text.Encoding.UTF8.GetBytes(str); //默认是System.Text.Encoding.Default.GetBytes(str)
            for (int i = 0; i < byStr.Length; i++)
            {
                sb.Append(@"%" + Convert.ToString(byStr[i], 16));
            }

            return (sb.ToString());
        }
        /// <summary>
        /// 钉钉回货通知审批推送钉钉
        /// </summary>
        /// <param name="idIn"></param>
        /// <param name="judge"></param>
        /// <returns></returns>
      
        public async Task<AjaxResult> SaveReturnToExamine(string Id, bool judge)
        {
            int Return = 0;
            if (judge)
            {
                await ExecuteSqlAsync("insert into Base_OverFlowTask values ('"+Id+"','ReturnNotice',0)");
                TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
                string sjtime = Convert.ToInt64(ts.TotalSeconds).ToString();
                string id = IdHelper.GetId();
                string sqlstr = "UPDATE PB_DeliveryNotice  set AuditStatus=1 where Id='" + Id + "' ;";
                Return = await ExecuteSqlAsync(sqlstr);
                //推送第一级
                List<string> ulist = new List<string>();
                //获取回货通知单数据
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT ");
                sql.Append("b.Id,");
                sql.Append("b.Content,");
                sql.Append("ifnull(c.LEVEL,0) LEVEL ");
                sql.Append("FROM ");
                sql.Append("PB_DeliveryNotice a ");
                sql.Append("LEFT JOIN Base_OverFlow b ON a.WorkFlow = b.Id ");
                sql.Append("LEFT JOIN Base_OverFlowTask c ON a.Id = c.BillId  ");
                sql.Append("WHERE ");
                sql.Append("a.Id = '" + Id + "'");
                Util.Helper.DingInputDTO input = new Util.Helper.DingInputDTO();
                string uids = "";
                var flowMob = await Db.GetListBySqlAsync<CurrentOverFlowDTO>(sql.ToString());
                JArray farrMob = JArray.Parse(flowMob[0].Content);
                JObject _objMob = JObject.Parse(farrMob[flowMob[0].Level].ToString());
                JArray usrMob = JArray.Parse(farrMob[0]["Assignees"]["UserId"].ToString());
                //推送显示文本
                var Ttile = await Db.GetIQueryable<PB_DeliveryNotice>().Where(x => x.Id == Id).FirstOrDefaultAsync();
                string title = Ttile.ProjectName;
                input.Str = $"回货通知:{title} 待审批" + DateTime.Now.ToString("");
                string url = "eapp://pages/info/info?billid=" + Id + "&title=" + _objMob["StartState"]["Name"] + "&btn=" + _objMob["Action"]["Name"].ToString() + "&id=" + flowMob[0].Id + "";
                input.MessgUrl = url;
                //推送表单
                input.BillId = Id;
                //获取创建回货通知单创建者
                if (usrMob.Count > 0)
                {
                    foreach (var u in usrMob)
                    {
                        uids += "'" + u + "',";
                    }
                    var emps = await Db.GetListBySqlAsync<Base_User>("SELECT * FROM Base_User WHERE Id IN (" + uids.Substring(0, uids.Length - 1) + ")");
                    foreach (var e in emps)
                    {
                        ulist.Add(e.DingUserId);
                    }
                    //推送用户集合
                    input.ExecutorIds = ulist;
                    //钉钉推送消息
                    DingHelper.sendMessage(input);
                }

               
               

            }
            else
            {
                Return = Db.ExecuteSql("UPDATE PB_DeliveryNotice  set AuditStatus=2 where Id='" + Id + "' ");
            }
            if (Return > 0)
            {
                
                AjaxResult res = new AjaxResult
                {
                
                Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }
        }

        public  async Task<AjaxResult> SaveCloss(string idIn)
        {
            int Return = 0;
            Return = Db.ExecuteSql("UPDATE  PB_DeliveryNotice set AuditStatus=3,UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'  where Id='" + idIn + "' ");
            if (Return > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }
            }

        /// <summary>
        /// 新建备货进行通知单查询 是否该通知单为关闭状态
        /// </summary>
        /// <param name="idIn"></param>
        /// <returns></returns>
        public async  Task<AjaxResult> GetallCount(string idIn)
        {
            Boolean SUS = false;
            var datasubMDatacount = Db.GetListBySqlAsync<PB_DeliveryNoticeItem>(@" SELECT  *  FROM PB_DeliveryNoticeItem as a INNER JOIN PB_DeliveryNotice AS b ON a.ParentId = b.Id   where a.id='" + idIn + "'  and b.AuditStatus=3").Result;
            if (datasubMDatacount.Count > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "通知单已关闭！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "正常！"
                };
                return res;
            }
        }


        //新建备货单验证是否有盘点任务
        public async Task<AjaxResult> CheckVerification()
        {

            var userdata =await Db.GetIQueryable<Base_User>().Where (m=>m.Id==_operator.UserId).FirstOrDefaultAsync();

            List<TD_Check> _tdchecks =await Db.GetIQueryable<TD_Check>().Where(m=>m.DeptId== userdata.FactoryId&&m.Status<=4).ToListAsync();
            if (_tdchecks.Count > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "有盘点单禁止创建"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "正常！"
                };
                return res;
            }
        }

        /// <summary>
        /// 通过 选择项目id 获取该项目下的第一条发货通知单数据 并且按照创建的时间排序
        /// 朱庆龙
        /// 2022/2/24
        /// </summary>
        /// <param name="ProjectId"></param>
        /// <returns></returns>
        public async Task <PB_DeliveryNoticeDtoinfo> SelectProjectId(string ProjectId)
        {
            PB_DeliveryNoticeDtoinfo dataTypeInfo = new PB_DeliveryNoticeDtoinfo();
            Expression<Func<PB_DeliveryNotice, PB_Customer, PB_Customer, PB_Customer, Base_OverFlow, PB_DeliveryNoticeDto>> select = (a, b, c, d,e) => new PB_DeliveryNoticeDto
            {
                 GouxiaoID=b.Id,
                 GouxiaoName=b.Name,
                 HezuoID=c.Id,
                 HeZuoName=c.Name,
                 QiandingID=d.Id,
                 QiandingName=d.Name,
                 Requirement=a.Remark,
                 Type=a.TypeId,
                  Tims=a.IssueDate, 
                 Address=a.Address,
                 WorkFlowName=e.Title,
                Typestate=a.Type,
                

            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DeliveryNotice>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Customer>() on a.SaleUnitId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Customer>() on a.CooperativeUnitId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Customer>() on a.SignUnitId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_OverFlow>() on a.WorkFlow equals e.Id into de
                    from e in de.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d,e);
           q= q.OrderByDescending(m=>m.CreateTime);
            q = q.Where(M=>M.Typestate==1);
            var data = await q.Where(x => x.ProjectId == ProjectId).FirstOrDefaultAsync();
            if (data != null)
            {
                dataTypeInfo.Data = data;
            }
            else {
                var PB_MobilizationNotice =await Db.GetIQueryable<PB_MobilizationNotice>().OrderByDescending(m=>m.CreateTime).Where(m=>m.ProjectId==ProjectId).FirstOrDefaultAsync();
                if (PB_MobilizationNotice!=null) {
                    PB_DeliveryNoticeDto pB_DeliveryNotice = new PB_DeliveryNoticeDto();
                    pB_DeliveryNotice.CreateTime = PB_MobilizationNotice.CreateTime;
                    pB_DeliveryNotice.Type = PB_MobilizationNotice.TypeId;
                    pB_DeliveryNotice.GouxiaoID = PB_MobilizationNotice.SaleUnitId;
                    pB_DeliveryNotice.GouxiaoName = PB_MobilizationNotice.SaleUnitName;
                    pB_DeliveryNotice.HezuoID = PB_MobilizationNotice.CooperativeUnitId;
                    pB_DeliveryNotice.HeZuoName = PB_MobilizationNotice.CooperativeUnitName;
                    pB_DeliveryNotice.QiandingID = PB_MobilizationNotice.SignUnitId;
                    pB_DeliveryNotice.QiandingName = PB_MobilizationNotice.SignUnitName;
                    pB_DeliveryNotice.Tims = PB_MobilizationNotice.IssueDate;
                    pB_DeliveryNotice.ContractNo = PB_MobilizationNotice.ContractNo;
                    pB_DeliveryNotice.SignUnitName = PB_MobilizationNotice.SignUnitName;
                    pB_DeliveryNotice.SignUnitId = PB_MobilizationNotice.SignUnitId;
                    pB_DeliveryNotice.CooperativeUnitName = PB_MobilizationNotice.CooperativeUnitName;
                    pB_DeliveryNotice.CooperativeUnitId = PB_MobilizationNotice.CooperativeUnitId;
                    pB_DeliveryNotice.SaleUnitId = PB_MobilizationNotice.SaleUnitId;
                    pB_DeliveryNotice.SaleUnitName = PB_MobilizationNotice.SaleUnitName;
                    pB_DeliveryNotice.TypeId = PB_MobilizationNotice.TypeId;
                    pB_DeliveryNotice.IssueDate = PB_MobilizationNotice.IssueDate;
                    pB_DeliveryNotice.BusinessLeader = PB_MobilizationNotice.BusinessLeader;
                    pB_DeliveryNotice.DocumentaryContact = PB_MobilizationNotice.DocumentaryContact;
                    dataTypeInfo.Data = pB_DeliveryNotice;
                }
            }
           
            return dataTypeInfo;
        }


        /// <summary>
        /// 更具发货通知单明细查询发货详情
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<NoticeBuildingnumberDTO>> GetNoticeBuildingnumberListAsync(PageInput<NoticeBuildingnumberConditionDTO> input)
        {
            var search = input.Search;
            string sqlMain = @"select t1.*,m.`Code` as MaterialCode,m.`Name` as MaterialName,m.Spec as MaterialllSpec,m.ProductCode,IFNULL(t2.OutNum,0) as OutNum,(t1.RealNums-IFNULL(t2.OutNum,0)) as NotShippedNums,ROW_NUMBER() over(order by t1.MaterialId desc) as rowsid  from (
select NoticeId,DesignId,MaterialId,SUM(Nums) as Nums,SUM(RealNums) as RealNums from (
select a.Id as NoticeId,b.ParentId as DesignId,MaterialId,Nums,RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where State!=2 and a.Id='{0}'
UNION ALL
select a.Id as NoticeId,b.ParentId as DesignId,MaterialId,-Nums as Nums,-RealNums as RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where State=2 and a.Id='{0}'
) as t group by NoticeId,DesignId,MaterialId
)as t1 
left join PB_Material as m on t1.MaterialId=m.Id
left join 
(
select NoticeId,MaterialId,SUM(OutNum) as OutNum from (
select c.ParentId as NoticeId,b.MaterialId,b.OutNum from TD_OutStorage as a
left join TD_OutStorRecord as b on a.Id=b.OutStorId
left join PB_PickUpMain as c on a.RefCode=c.Id
where c.ParentId='{0}'
) as t group by NoticeId,MaterialId

) as t2 on t1.NoticeId=t2.NoticeId and t1.MaterialId=t2.MaterialId";
            string sql = string.Format(sqlMain, search.NoticeId);
            var listdata = await Db.GetListBySqlAsync<NoticeBuildingnumberDTO>(sql);
            int num = listdata.Count;
            var list = listdata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<NoticeBuildingnumberDTO> { Data = list, Total = num };

        }

        /// <summary>
        /// 更具发货通知单明细查询物料发货明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<NoticeBuildingnumberDTO>> GetNoticeBuildingnumberDataAsync(PageInput<NoticeBuildingnumberConditionDTO> input)
        {
            var search = input.Search;
            string sqlMain = @"select t1.*,m.`Code` as MaterialCode,m.`Name` as MaterialName,m.Spec as MaterialllSpec,m.ProductCode,IFNULL(t2.OutNum,0) as OutNum,(t1.RealNums-IFNULL(t2.OutNum,0)) as NotShippedNums,ROW_NUMBER() over(order by t1.BuildingnumberName desc) as rowsid  from (
select NoticeId,DesignId,MaterialId,BuildingnumberName,SUM(Nums) as Nums,SUM(RealNums) as RealNums from (
select a.Id as NoticeId,b.ParentId as DesignId,MaterialId,Nums,RealNums,CONCAT(d.Buildingnumber,'/',e.Name) as BuildingnumberName from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId left join PB_DesignList as c on b.ParentId=c.Id left join PB_Buildingnumber as d on c.BuildingNumberID=d.Id left join PB_DesignListType as e on c.Type=e.Id where State!=2 and a.ParentId='{0}'
UNION ALL
select a.Id as NoticeId,b.ParentId as DesignId,MaterialId,Nums,RealNums,CONCAT(d.Buildingnumber,'/',e.Name) as BuildingnumberName from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId left join PB_DesignList as c on b.ParentId=c.Id left join PB_Buildingnumber as d on c.BuildingNumberID=d.Id left join PB_DesignListType as e on c.Type=e.Id where State=2 and a.ParentId='{0}'
) as t group by NoticeId,DesignId,MaterialId,BuildingnumberName
)as t1 
left join PB_Material as m on t1.MaterialId=m.Id
left join 
(
select NoticeId,MaterialId,SUM(OutNum) as OutNum from (
select c.ParentId as NoticeId,b.MaterialId,b.OutNum from TD_OutStorage as a
left join TD_OutStorRecord as b on a.Id=b.OutStorId
left join PB_PickUpMain as c on a.RefCode=c.Id
left join PB_DeliveryNoticeItem as d on c.ParentId=d.Id
where d.ParentId='{0}'
) as t group by NoticeId,MaterialId
) as t2 on t1.NoticeId=t2.NoticeId and t1.MaterialId=t2.MaterialId";
            string sql = string.Format(sqlMain, search.NoticeId);
            var listdata = await Db.GetListBySqlAsync<NoticeBuildingnumberDTO>(sql);
            int num = listdata.Count;
            var list = listdata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<NoticeBuildingnumberDTO> { Data = list, Total = num };
        }

        #endregion

        #region 私有成员

        #endregion
    }
}